
import sqlite3
#1.连接数据库


def createTable0():
    # #2.创建表
    conn=sqlite3.connect("gongXunBox.db") #打开或创建数据库文件
    print("成功打开数据库")
    c=conn.cursor()#获取游标
    sql=f'''
    create table if not exists  company
    (id int primary key not null,
    name text not null,
    age int not null,
    address char(50),
    salary real);
    '''

    box_sql = '''
    create table if not exists box_contain
    (id int primary key not null,
     tid int 
    );
    '''

    device_sql = '''
    create table if not exists device
    (id int primary key not null,
     name text not null,
     status int not null
    );
    '''

    signUp_sql = '''
    create table if not exists signup
    (id text not null,
     openid char(50),
     name text not null,
     thingid int primary key ,
     thingname char(50),
     date text
    );
    '''

    history_sql = '''
        create table if not exists  history
            (hid INTEGER PRIMARY KEY AUTOINCREMENT ,
            sno char(15) not null,
            sname char(50) not null,
            openid char(50),
            thingid int,
            date char(50),
            status int not null
            );
    '''



    c.execute(signUp_sql)#执行sql语句
    c.execute(box_sql)  # 执行sql语句
    c.execute(device_sql)  # 执行sql语句
    conn.commit()#提交数据库操作
    conn.close()#关闭数据连接
    print("成功建表")


def superControl(cmd,dict,choice=0):
    # choice = 1,2,3,4,5 代表 增 删 改 查,查全部

    conn = sqlite3.connect("gongXunBox.db")  # 打开或创建数据库文件
    print("成功打开数据库")
    c = conn.cursor()  # 获取游标
    sql = ""

    if cmd == "Device":
        if choice == 1:
            sql = f'''
                        insert into device(id,name,status)
                        values({dict["id"]},"{dict["name"]}",{dict["status"]})
                    '''
        elif choice == 2:
            sql = f'''
                        delete from device
                        where id = {dict["id"]}
                    '''
        elif choice == 3:
            sql = f'''
                        update device
                        set status = {dict["status"]}
                        where id = {dict["id"]}
                    '''
        elif choice == 4:
            sql = f'''
                        select * from device
                        where id = {dict["id"]}
                    '''
        elif choice == 5:
            sql = f'''
                        select * from device
                    '''
    elif cmd == "Box_Contain":
        if choice == 1:
            sql = f'''
                        insert into box_contain(id,tid)
                        values({dict["id"]},{dict["tid"]})
                    '''
        elif choice == 2:
            sql = f'''
                        delete from box_contain
                        where id = {dict["id"]}
                    '''
        elif choice == 3:
            sql = f'''
                        update box_contain
                        set tid = {dict["tid"]}
                        where id = {dict["id"]}
                    '''
        elif choice == 4:
            sql = f'''
                        select * from box_contain
                        where id = {dict["id"]}
                    '''
        elif choice == 5:
            sql = f'''
                        select * from box_contain
                    '''
    elif cmd == "Sign_up":
        if choice == 1:
            sql = f'''
                        insert into signup(id,openid,name,thingid,thingname,date)
                        values("{dict["id"]}","{dict["openid"]}","{dict["name"]}",{dict["thingid"]},"{dict["thingname"]}","{dict["date"]}")
                    '''
        elif choice == 2:
            sql = f'''
                        delete from signup
                        where thingid = {dict["thingid"]}
                    '''
        elif choice == 3:
            sql = f'''
                        update box_contain
                        set tid = {dict["tid"]}
                        where id = {dict["id"]}
                    '''
        elif choice == 4:
            sql = f'''
                        select * from signup
                        where thingid = {dict["thingid"]}
                    '''
        elif choice == 5:
            sql = f'''
                        select * from signup
                    '''
    elif cmd == "history":
        if choice == 1:
            sql = f'''
                        insert into history(sno,sname,openid,thingid,date,status)
                        values("{dict["sno"]}","{dict["sname"]}","{dict["openid"]}",{dict["thingid"]},"{dict["date"]}",{dict["status"]})
                    '''
        elif choice == 2:
            sql = f'''
                        delete from history
                        where hid = {dict["hid"]}
                    '''
        elif choice == 3:
            sql = f'''
                        update history
                        set status = {dict["status"]}
                        where hid = {dict["hid"]}
                    '''
        elif choice == 4:
            sql = f'''
                        select * from history
                        where sno = "{dict["sno"]}"
                    '''
        elif choice == 5:
            sql = f'''
                        select * from history
                    '''
    try:


        print(sql)
        c.execute(sql)  # 执行sql语句
        if choice >= 4:
            rv = c.fetchall()
            # print(rv)
            payload = []

            for result in rv:
                content = {'id': result[0], 'tid': result[1]}
                payload.append(result)


            conn.commit()  # 提交数据库操作
            conn.close()  # 关闭数据连接
            return payload
        else:
            conn.commit()  # 提交数据库操作
            conn.close()  # 关闭数据连接
            return "success"
    except Exception as e:
        print("失败:",e)




if __name__ == '__main__':
    print("hi")
    createTable0()
